﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace ATM
{
    class ATM
    {
        private const string CONNECTION_STRING = "Server=.\\SQLEXPRESS; Integrated Security=true; database = ATM";
        static void Main()
        {
            SqlConnection dbCon = new SqlConnection(CONNECTION_STRING);
            ////string command = "CREATE DATABASE ATM";
            //string command = "CREATE TABLE CardAccounts (CardAccountsID int IDENTITY,CardNumber char(10) NOT NULL, " +
            //    "CardPIN char(4) NOT NULL, CardCash money NOT NULL)";
            //SqlCommand myCommand = new SqlCommand(command, dbCon);
            //dbCon.Open();
            //using (dbCon)
            //{
            //    myCommand.ExecuteNonQuery();
            //}

            //dbCon.Open();
            //using (dbCon)
            //{
            //    int cardNumber = 1254;
            //    int pIN = 1111;
            //    decimal sum = 200;
            //    BeginWithdrawMoney(dbCon, cardNumber, pIN, sum);
            //    BeginWithdrawMoney(dbCon, 12632, 2222, 300);
            //    BeginWithdrawMoney(dbCon, 3256, 333, 400);
            //    BeginWithdrawMoney(dbCon, cardNumber, pIN, 150);
            //    BeginWithdrawMoney(dbCon, cardNumber, pIN, 125);
            //    BeginWithdrawMoney(dbCon, cardNumber, pIN, 130);

            //}

            dbCon.Open();
            using (dbCon)
            {
                SqlCommand cmd = dbCon.CreateCommand();
                int cardNumber = 1254;
                int pIN = 1111;
                try
                {
                    GetTransactionInformation(cmd, cardNumber, pIN);
                }
                catch (ArgumentException e)
                {
                    Console.WriteLine("Exception occured: {0}", e.Message);
                }
            }
        }

        private static void BeginWithdrawMoney(SqlConnection dbCon, int cardNumber, int pIN, decimal sum)
        {
            SqlTransaction trans = dbCon.BeginTransaction(IsolationLevel.RepeatableRead);
            SqlCommand cmd = dbCon.CreateCommand();
            cmd.Transaction = trans;

            try
            {
                WithdrawMoney(cardNumber, pIN, sum, trans, cmd);
            }
            catch (SqlException e)
            {
                Console.WriteLine("Exception occured: {0}", e.Message);
                trans.Rollback();
                Console.WriteLine("Transaction cancelled.");
            }
            catch (ArgumentException e)
            {
                Console.WriteLine("Exception occured: {0}", e.Message);
                trans.Rollback();
                Console.WriteLine("Transaction cancelled.");
            }
            catch (InsufficientMoneyException e)
            {
                Console.WriteLine("Exception occured: {0}", e.Message);
                trans.Rollback();
                Console.WriteLine("Transaction cancelled.");
            }
        }

        private static void WithdrawMoney(int cardNumber, int pIN, decimal sum, SqlTransaction trans, SqlCommand cmd)
        {
            cmd.CommandText = "SELECT * " +
                "FROM CardAccounts " +
                "WHERE CardNumber = @CardNumber";
            SqlParameter sqlParameterCardNumber = new SqlParameter("@CardNumber", cardNumber);
            cmd.Parameters.Add(sqlParameterCardNumber);
            SqlDataReader reader = cmd.ExecuteReader();
            decimal accountSum;
            using (reader)
            {
                reader.Read();
                int realPIN = int.Parse((string)reader["CardPIN"]);
                if (pIN != realPIN)
                {
                    throw new ArgumentException("Wrong PIN entered!");
                }
                accountSum = (decimal)reader["CardCash"];
                if (accountSum < sum)
                {
                    throw new InsufficientMoneyException("Not enough money.");
                }
            }
            CompleteTransaction(sum, trans, cmd, accountSum);
            AddNewLog(cardNumber, sum, cmd);
        }

        private static void CompleteTransaction(decimal sum, SqlTransaction trans, SqlCommand cmd, decimal accountSum)
        {
            accountSum -= sum;
            SqlParameter sqlParameterSum = new SqlParameter("@Sum", accountSum);
            cmd.Parameters.Add(sqlParameterSum);
            cmd.CommandText =
            "UPDATE CardAccounts " +
            "SET CardCash = @Sum " +
            "WHERE CardNumber = @CardNumber";
            cmd.ExecuteNonQuery();
            trans.Commit();
            Console.WriteLine("Transaction comitted.");
        }

        private static void AddNewLog(int cardNumber, decimal sum, SqlCommand cmd)
        {
            SqlParameter sqlParameterTransactionDate = new SqlParameter("@TransactionDate", DateTime.Now);
            cmd.Parameters.Add(sqlParameterTransactionDate);
            SqlParameter sqlParameterAmmount = new SqlParameter("@Ammount", sum);
            cmd.Parameters.Add(sqlParameterAmmount);
            cmd.CommandText = "INSERT INTO AccountTransactions " +
                  "(CardNumber, TransactionDate, Ammount) " +
                  "VALUES (@CardNumber, @TransactionDate, @Ammount)";
            cmd.ExecuteNonQuery();
        }
        
        private static void GetTransactionInformation(SqlCommand cmd, int cardNumber, int pIN)
        {
            cmd.CommandText = "SELECT * " +
            "FROM CardAccounts " +
            "WHERE CardNumber = @CardNumber";
            SqlParameter sqlParameterCardNumber = new SqlParameter("@CardNumber", cardNumber);
            cmd.Parameters.Add(sqlParameterCardNumber);
            SqlDataReader reader = cmd.ExecuteReader();
            using (reader)
            {
                reader.Read();
                int realPIN = int.Parse((string)reader["CardPIN"]);
                if (pIN != realPIN)
                {
                    throw new ArgumentException("Wrong PIN entered!");
                }
            }
            cmd.CommandText = "SELECT * " +
            "FROM AccountTransactions " +
            "WHERE CardNumber = @CardNumber";
            reader = cmd.ExecuteReader();
            using (reader)
            {
                Console.WriteLine("TransactionDate - TransactionAmmount");
                while (reader.Read())
                {
                    DateTime transactionDate = (DateTime)reader["TransactionDate"];
                    decimal transactionAmmount = (decimal)reader["Ammount"];
                    Console.WriteLine("{0} - {1}",
                    transactionDate, transactionAmmount);
                }
            }
        }
    }
}